﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using IBatisNet.DataAccess;
using IBatisNet.DataAccess.Exceptions;

using Wolfut.MirageEE.Core.DAO;
using Wolfut.MirageEE.Core.Test.Domain;

namespace Wolfut.MirageEE.Core.Test.Dao
{
    public class CategoryADODao :BaseADODao,ICategoryDao 
    {

        // Sql Server
        private const string SELECT_CATEGORY_ALL= "select Category_Id, Category_Name, Category_Description from Categories";
        private const string SELECT_CATEGORY_BY_ID = "select Category_Id, Category_Name, Category_Description from Categories where Category_Id = @Category_Id";
        private const string INSERT_ACCOUNT = "insert into Categories (Category_Id, Category_Name, Category_Description) values (@Category_Id, @Category_Name, @Category_Description)";
        private const string UPDATE_ACCOUNT = "update Categories set Category_Name = @Category_Name, Category_Description = @Category_Description where Category_Id = @Category_Id";
       

        // OLEDB doesn't support named parameters !!!
        // Caution : You must declare the parameters in the order declaration
        private const string OLEDB_SELECT_CATEGORY_ALL = "select Category_Id, Category_Name, Category_Description from Categories";
        private const string OLEDB_SELECT_CATEGORY_BY_ID = "select Category_Id, Category_Name, Category_Description from Categories where Category_Id = @Category_Id";
        private const string OLEDB_INSERT_ACCOUNT = "insert into Categories (Category_Id, Category_Name, Category_Description) values (@Category_Id, @Category_Name, @Category_Description)";
        private const string OLEDB_UPDATE_ACCOUNT = "update Categories set Category_Name = @Category_Name, Category_Description = @Category_Description where Category_Id = @Category_Id";

        private const string PARAM_CATEGORY_ID = "@Category_Id";
        private const string PARAM_CATEGORY_NAME = "@Category_Name";
        private const string PARAM_CATEGORY_DESCRIPTION = "@Category_Description";
 

        #region ICategoryDao Members

        public System.Collections.Generic.IList<Category> GetCategoryList()
        {
            
            DaoSession daoSession = this.GetContext() as DaoSession;
            IDbCommand command = daoSession.CreateCommand(CommandType.Text);
            IList<Category> categories = new List<Category>();
            try
            {
                if (daoSession.DataSource.DbProvider.UseParameterPrefixInSql)
                {
                    command.CommandText = SELECT_CATEGORY_ALL;
                }
                else
                {
                    command.CommandText = SELECT_CATEGORY_BY_ID;
                }
              
                IDataReader dataReader;

                dataReader = command.ExecuteReader();

                while(dataReader.Read())
                {
                    categories.Add(new Category() { Id = dataReader["Category_Id"].ToString(), Name = dataReader["Category_Name"].ToString(), Description = dataReader["Category_Description"].ToString() });
                }

                dataReader.Close();
                command.Parameters.Clear();

            }
            catch (Exception e)
            {

                throw new DataAccessException("Error executing CategoryADODao.GetCategoryList. Cause :" + e.Message, e);
            }
            finally
            {
                command.Dispose();
            }
            return categories;

        }

        public Wolfut.MirageEE.Core.Test.Domain.Category GetCategory(string categoryId)
        {
            DaoSession daoSession = this.GetContext() as DaoSession;
            IDbCommand command = daoSession.CreateCommand(CommandType.Text);
            Category category = new Category();
            try
            {
                if (daoSession.DataSource.DbProvider.UseParameterPrefixInSql)
                {
                    command.CommandText = SELECT_CATEGORY_ALL;
                }
                else
                {
                    command.CommandText = SELECT_CATEGORY_BY_ID;
                }
                IDbDataParameter sqlParameter = command.CreateParameter();
                sqlParameter.ParameterName = PARAM_CATEGORY_ID;
                sqlParameter.Value = categoryId;
                command.Parameters.Add(sqlParameter);

                IDataReader dataReader;

                dataReader = command.ExecuteReader();

                if (dataReader.Read())
                {
                    category = new Category()
                                   {
                                       Id = dataReader["Category_Id"].ToString(),
                                       Name = dataReader["Category_Name"].ToString(),
                                       Description = dataReader["Category_Description"].ToString()
                                   };
                }

                dataReader.Close();
                command.Parameters.Clear();

            }
            catch (Exception e)
            {

                throw new DataAccessException("Error executing CategoryADODao.GetCategoryList. Cause :" + e.Message, e);
            }
            finally
            {
                command.Dispose();
            }
            return category;
        }

        #endregion
    }
}
